////////////////////////////////////////////////////////////////////////////////
//
// Copyright (c) 2009  Jake Lin.  All Rights Reserved. http://procoder.cnblogs.com
//
//
// Based on wrapper written by Tyushkov Nikolay
// http://softvoile.com/development/CppSQLite3U/
// 
////////////////////////////////////////////////////////////////////////////////

#pragma once

#include "CppSQLite3U.h"
#include <string>

#define DB_FILE_NAME L"Sqlite.db"
int max = 100;

SimpleString StringFrom(const std::string& value)
{
    return SimpleString(value.c_str());
}

std::string ToString(LPCTSTR lpctstr)
{
	char* cstr = new char[_tcslen(lpctstr)+ 1];
	sprintf(cstr, "%S", lpctstr);
	std::string str(cstr);
	delete [] cstr;
	return str;
}

//http://blogs.msdn.com/cenet/archive/2006/04/29/time-h-on-windows-ce.aspx
time_t time( time_t *inTT ) 
{
	SYSTEMTIME sysTimeStruct;
	FILETIME fTime;
	ULARGE_INTEGER int64time;
	time_t locTT = 0;

	if ( inTT == NULL ) 
	{
		inTT = &locTT;
	}

	GetSystemTime( &sysTimeStruct );
	if ( SystemTimeToFileTime( &sysTimeStruct, &fTime ) ) 
	{
		memcpy( &int64time, &fTime, sizeof( FILETIME ) );
		/* Subtract the value for 1970-01-01 00:00 (UTC) */
		int64time.QuadPart -= 0x19db1ded53e8000;
		/* Convert to seconds. */
		int64time.QuadPart /= 10000000;
		*inTT = int64time.QuadPart;
	}

	return *inTT;
} 

TEST(SqliteHelper, UpdateTableWithTransaction)
{
	try
	{
		CppSQLite3DB db;
		db.open(DB_FILE_NAME);
		
		CString sqlStr;
		time_t tmStart, tmEnd;
		tmStart = time(0);
		db.execDML(L"begin transaction;");
		for(int i=0; i<max; ++i)
		{
			SYSTEMTIME currentTime;	
			GetLocalTime(&currentTime);
			sqlStr.Format(L"UPDATE T1 SET F2='STR%d', F3='%d-%d-%d %d:%d:%d' WHERE F1=%d", 
				max-i, currentTime.wYear, currentTime.wMonth, currentTime.wDay, currentTime.wHour, currentTime.wMinute, currentTime.wSecond, i);
			db.execDML(sqlStr);
		}
		db.execDML(L"commit transaction;");
		tmEnd = time(0);
		char ch[255];
		sprintf(ch, "Update table successful in %d seconds", tmEnd-tmStart);
		TRACE(ch);
		db.close();
	}
	catch(CppSQLite3Exception e)
	{
		FAIL(ToString(e.errorMessage()).c_str());
	}
}

TEST(SqliteHelper, InsertTableWithTransaction)
{
	try
	{
		CppSQLite3DB db;
		db.open(DB_FILE_NAME);
		
		CString sqlStr;
		time_t tmStart, tmEnd;
		tmStart = time(0);
		db.execDML(L"begin transaction;");
		for(int i=0; i<max; ++i)
		{
			SYSTEMTIME currentTime;	
			GetLocalTime(&currentTime);
			sqlStr.Format(L"INSERT INTO T1 (F1, F2, F3) VALUES(%d, 'STR%d', '%d-%d-%d %d:%d:%d')", 
				i, i, currentTime.wYear, currentTime.wMonth, currentTime.wDay, currentTime.wHour, currentTime.wMinute, currentTime.wSecond);
			db.execDML(sqlStr);
		}
		db.execDML(L"commit transaction;");
		tmEnd = time(0);
		char ch[255];
		sprintf(ch, "Insert table successful in %d seconds", tmEnd-tmStart);
		TRACE(ch);
		db.close();
	}
	catch(CppSQLite3Exception e)
	{
		FAIL(ToString(e.errorMessage()).c_str());
	}
}

TEST(SqliteHelper, DeleteTable)
{
	try
	{
		CppSQLite3DB db;
		db.open(DB_FILE_NAME);
		
		CString sqlStr;
		time_t tmStart, tmEnd;
		tmStart = time(0);
		for(int i=0; i<max; ++i)
		{
			sqlStr.Format(L"DELETE FROM T1 WHERE F1=%d", i);
			db.execDML(sqlStr);
		}
		tmEnd = time(0);
		char ch[255];
		sprintf(ch, "Delete table successful in %d seconds", tmEnd-tmStart);
		TRACE(ch);
		db.close();
	}
	catch(CppSQLite3Exception e)
	{
		FAIL(ToString(e.errorMessage()).c_str());
	}
}

TEST(SqliteHelper, SelectScalarAfterUpdate)
{
	try
	{
		CppSQLite3DB db;
		db.open(DB_FILE_NAME);
		
		int count = db.execScalar(L"SELECT COUNT(*) FROM T1;");
		char ch[255];
		sprintf(ch, "%d rows in T1 table", count);
		TRACE(ch);
		db.close();
	}
	catch(CppSQLite3Exception e)
	{
		FAIL(ToString(e.errorMessage()).c_str());
	}
	TRACE("Select scalar after update successful.");
}

TEST(SqliteHelper, UpdateTable)
{
	try
	{
		CppSQLite3DB db;
		db.open(DB_FILE_NAME);
		
		CString sqlStr;
		time_t tmStart, tmEnd;
		tmStart = time(0);
		for(int i=0; i<max; ++i)
		{
			SYSTEMTIME currentTime;	
			GetLocalTime(&currentTime);
			sqlStr.Format(L"UPDATE T1 SET F2='STR%d', F3='%d-%d-%d %d:%d:%d' WHERE F1=%d", 
				max-i, currentTime.wYear, currentTime.wMonth, currentTime.wDay, currentTime.wHour, currentTime.wMinute, currentTime.wSecond, i);
			db.execDML(sqlStr);
		}
		tmEnd = time(0);
		char ch[255];
		sprintf(ch, "Update table successful in %d seconds", tmEnd-tmStart);
		TRACE(ch);
		db.close();
	}
	catch(CppSQLite3Exception e)
	{
		FAIL(ToString(e.errorMessage()).c_str());
	}
}

TEST(SqliteHelper, SelectAfterInsert)
{
	try
	{
		CppSQLite3DB db;
		db.open(DB_FILE_NAME);
		CppSQLite3Query q = db.execQuery(L"SELECT * FROM T1;");
		
		std::string str;
		char ch[255];
        while (!q.eof())
        {
			sprintf(ch, "F1=%d, F2=%S, F3=%S\n", q.getIntField(0), q.getStringField(1), q.getStringField(2));
			str += ch;
			q.nextRow();
        }
		TRACE(str.c_str());
		db.close();
	}
	catch(CppSQLite3Exception e)
	{
		FAIL(ToString(e.errorMessage()).c_str());
	}
}

TEST(SqliteHelper, SelectScalarAfterInsert)
{
	try
	{
		CppSQLite3DB db;
		db.open(DB_FILE_NAME);
		
		int count = db.execScalar(L"SELECT COUNT(*) FROM T1;");
		char ch[255];
		sprintf(ch, "%d rows in T1 table", count);
		TRACE(ch);
		db.close();
	}
	catch(CppSQLite3Exception e)
	{
		FAIL(ToString(e.errorMessage()).c_str());
	}
	TRACE("Select scalar after insert successful.");
}

TEST(SqliteHelper, InsertTable)
{
	try
	{
		CppSQLite3DB db;
		db.open(DB_FILE_NAME);
		
		CString sqlStr;
		time_t tmStart, tmEnd;
		tmStart = time(0);
		for(int i=0; i<max; ++i)
		{
			SYSTEMTIME currentTime;	
			GetLocalTime(&currentTime);
			sqlStr.Format(L"INSERT INTO T1 (F1, F2, F3) VALUES(%d, 'STR%d', '%d-%d-%d %d:%d:%d')", 
				i, i, currentTime.wYear, currentTime.wMonth, currentTime.wDay, currentTime.wHour, currentTime.wMinute, currentTime.wSecond);
			db.execDML(sqlStr);
		}
		tmEnd = time(0);
		char ch[255];
		sprintf(ch, "Insert table successful in %d seconds", tmEnd-tmStart);
		TRACE(ch);
		db.close();
	}
	catch(CppSQLite3Exception e)
	{
		FAIL(ToString(e.errorMessage()).c_str());
	}
}

TEST(SqliteHelper, SelectScalarBeforeInsert)
{
	try
	{
		CppSQLite3DB db;
		db.open(DB_FILE_NAME);
		
		int count = db.execScalar(L"SELECT COUNT(*) FROM T1;");
		char ch[255];
		sprintf(ch, "%d rows in T1 table", count);
		TRACE(ch);
		db.close();
	}
	catch(CppSQLite3Exception e)
	{
		FAIL(ToString(e.errorMessage()).c_str());
	}
	TRACE("Select scalar before insert successful.");
}

TEST(SqliteHelper, CreateTable)
{
	try
	{
		CppSQLite3DB db;
		db.open(DB_FILE_NAME);
		db.execDML(L"create table T1(F1 int, F2 char(20), F3 char(20));");
		
		db.close();
	}
	catch(CppSQLite3Exception e)
	{
		FAIL(ToString(e.errorMessage()).c_str());
	}
	TRACE("Create table successful.");
}

TEST(SqliteHelper, CreateDatabase)
{
	try
	{
		CppSQLite3DB db;
		DeleteFile(DB_FILE_NAME);
		db.open(DB_FILE_NAME);
		db.close();
	}
	catch(CppSQLite3Exception e)
	{
		FAIL(ToString(e.errorMessage()).c_str());
	}
	TRACE("Create database successful.");
}
